How to find the top 5 or n most frequent text or numbers in Excel?

It is a common task to get the most frequent text from a list of cells for most of us. In certain case, you may also need to find and extract the top 3 or n most frequent text from a list as below screenshot shown. To solve this problem, this article will introduce some quick tricks for you.

Find and extract top 3 or n most frequent text or numbers with formulas

Find and extract top 3 or n most frequent text or numbers with Pivot Table

You can achieve this task by using the below formulas, please do as this:

1. Please apply the below formula to get the value that appears the most frequently:

Notes:

1. In this formula, A2:A18 is the data list that you want to find the values from.

2. If you are using the Excel versions that are prior to Microsoft 365, you should press Ctrl + Shift + Enter to get the result. For the users of Microsoft 365 and newer versions of Excel, you can just enter the formula in the result cell, and then press Enter key.

2. Then, you should use the following array formula to get the second most frequent value, enter this formula below the first formula cell, and then press Ctrl + Shift + Enter keys together to get the correct result, see screenshot:

3. After extracting the second most frequent value, drag this formula cell down to get other n values as you need. See screenshot:

4. Finally, you can use the following formula to get the number of occurrences of the extracting text, see screenshot:

In fact, you can also use a single array formula to get the result, please do as this:

1. Enter or copy the below formula into a blank cell where you want to get the result, then, press Ctrl + Shift + Enter keys together to get the first result, see screenshot: